package com.zhidisoft;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.zdsoft.crm.dao.auth.ISysFunctionMapper;
import com.zdsoft.crm.entity.auth.SysFunction;
import com.zdsoft.crm.util.DateUtil;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class SysFunctionPoi {

	@Autowired
	ISysFunctionMapper mapper;

	@Test
	public void importData() throws FileNotFoundException, IOException {
		StringBuffer fileName = new StringBuffer("C:\\Users\\Administrator\\Desktop\\用户菜单-");
		fileName.append(DateUtil.parseStr(new Date(), "yyyy年MM月dd日")).append(".xls");
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileName.toString()));
		HSSFSheet sheet = workbook.getSheet("效益指标");
		System.out.println(sheet.getPhysicalNumberOfRows());

		// 高级写法
		for (int i = 0, length = sheet.getPhysicalNumberOfRows(); i < length; i++) {
			HSSFRow row = sheet.getRow(i);
			HSSFCell cell = row.getCell((short) 2);
			System.out.println(cell.getStringCellValue());
		}
	}

	@Test
	public void exportData() throws IOException {
		// 生成文件名
		StringBuffer fileName = new StringBuffer("C:\\Users\\Administrator\\Desktop\\用户菜单-");
		fileName.append(DateUtil.parseStr(new Date(), "yyyy年MM月dd日")).append(".xls");
		// 创建对Excel工作簿文件的引用
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在Excel工作簿中建一工作表，其名为缺省值
		// 如要新建一名为"效益指标"的工作表，其语句为：
		// HSSFSheet sheet = workbook.createSheet("效益指标");
		HSSFSheet sheet = workbook.createSheet("效益指标");
		// 查询数据
		List<SysFunction> functionList = mapper.findAll();
		int i = 0;
		for (SysFunction item : functionList) {
			HSSFRow row = sheet.createRow((short) i);
			HSSFCell cell1 = row.createCell((short) 0);
			// 定义单元格为字符串类型
			cell1.setCellValue(item.getId());

			HSSFCell cell2 = row.createCell((short) 1);
			// 定义单元格为字符串类型
			cell2.setCellValue(item.getFunc_code());

			HSSFCell cell3 = row.createCell((short) 2);
			// 定义单元格为字符串类型
			cell3.setCellValue(item.getFunc_name());

			i++;
		}
		// 新建一输出文件流
		File file = new File(fileName.toString());
		if (!file.exists())
			file.createNewFile();
		workbook.write(file);
	}

	// private void setCell(HSSFCell cell, Object value) {
	// if (value instanceof String) {
	// cell.setCellType(CellType.STRING);
	// cell.setCellValue(value.toString());
	// } else if (value instanceof Integer) {
	// cell.setCellType(CellType.NUMERIC);
	// cell.setCellValue(value);
	// }
	// }

}
